import duckdb, os
import re
import uuid
from service.nw_fymc import nw_fymc_service
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# 创建 NW_FYMC_MX 表
def create_table():
    con = get_connection()
    con.execute("""
        CREATE TABLE IF NOT EXISTS NW_FYMC_MX (
            uuid VARCHAR(32) PRIMARY KEY,
            dwbh VARCHAR(100),
            model VARCHAR(200),
            kjnd VARCHAR(10),
            fybh VARCHAR(100),
            fymc VARCHAR(100),
            kmbh VARCHAR(100),
            calc VARCHAR(100),
            jsfs VARCHAR(100),
            status CHAR(1) DEFAULT '1',
            create_by VARCHAR(200),
            create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            remarks VARCHAR(500)
        )
    """)
    con.close()

# 获取所有记录
def get_all_data():
    con = get_connection()
    query = "SELECT * FROM NW_FYMC_MX"
    result = con.execute(query).fetchall()
    con.close()
    return result

# 根据 ID 获取单条记录
def get_data_by_uuid(uuid):
    con = get_connection()
    query = "SELECT * FROM NW_FYMC_MX WHERE uuid = ?"
    result = con.execute(query, (uuid,)).fetchone()
    con.close()
    return result

# 添加记录
def add_data(uuid, dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        INSERT INTO NW_FYMC_MX (uuid, dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, remarks)
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (uuid, dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, remarks))
    con.close()

# 更新记录
def update_data(uuid, dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        UPDATE NW_FYMC_MX
        SET dwbh = ?, model = ?, kjnd = ?, fybh = ?, fymc = ?, kmbh = ?, calc = ?, jsfs = ?, status = ?, create_by = ?, remarks = ?
        WHERE uuid = ?
    """, (dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, remarks, uuid))
    con.close()

# 删除记录
def delete_data(uuid):
    con = get_connection()
    con.execute("DELETE FROM NW_FYMC_MX WHERE uuid = ?", (uuid,))
    con.close()

def delete_by_dwbh_model_kjnd(dwbh, model, kjnd):

    con = get_connection()
    con.execute("DELETE FROM NW_FYMC_MX WHERE dwbh = ? and model = ? and  kjnd = ?", (dwbh, model, kjnd))
    con.close()


# 由NW_FYMC计算NW_FYMC_MX
def calculate_nw_fymc_mx(dwbh, model, kjnd):
    """
    计算并生成 NW_FYMC_MX 表的数据。

    Args:
        dwbh (str): 单位编号。
        model (str): 模型。
        kjnd (str): 会计年度。

    Returns:
        list: 包含计算结果的数据列表。
    """
    nw_fymc_data = nw_fymc_service.get_data_by_condition(dwbh, model, kjnd)
    # print(nw_fymc_data)
    # print(type(nw_fymc_data))
    con = get_connection()

    # 关闭自动提交，开始一个显式的事务
    con.execute("BEGIN TRANSACTION")
    con.execute("DELETE FROM NW_FYMC_MX WHERE dwbh = ? and model = ? and  kjnd = ?", (dwbh, model, kjnd))
    for row in nw_fymc_data:
        dwbh = row[1]
        model = row[2]
        kjnd = row[3]
        fybh = row[4]
        fymc = row[5]
        kmbh_desc = row[6]  # 原数据填写规则：JFFS#501101,+JFFS#501102,-JFFS#501108
        status = row[7]
        create_by = row[8]
        remarks = row[9]
        # print(row)
        # print(re.sub(r'\s+', '', kmbh_desc).split(','))
        # print(type(re.sub(r'\s+', '', kmbh_desc).split(',')))
        for one_kmbh in re.sub(r'\s+', '', kmbh_desc).split(','):
            if one_kmbh[0] == '-':
                calc = '-'
                one_kmbh_without_flag = one_kmbh[1:]
            elif one_kmbh[0] == '+':
                calc = '+'
                one_kmbh_without_flag = one_kmbh[1:]
            else:
                calc = '+'
                one_kmbh_without_flag = one_kmbh
            kmbh = re.findall(r'\d+',  one_kmbh_without_flag)[0]
            jsfs_matches = re.findall(r'[a-zA-Z]+',  one_kmbh_without_flag)
            jsfs = jsfs_matches[0] if jsfs_matches else 'JFFS'
            # print(calc, jsfs, kmbh)
            add_data(uuid.uuid4(), dwbh, model, kjnd, fybh, fymc, kmbh, calc, jsfs, status, create_by, '自动计算得出')

    # 提交事务
    con.execute("COMMIT")
    # 如果有错误，可以回滚事务
    # con.execute("ROLLBACK")
    # 关闭连接
    con.close()

# 由NW_FYMC计算NW_FYMC_MX, 修改版
def calculate_nw_fymc_mx_v2(p_dwbh, p_model, p_kjnd = None):
    """
    计算并生成 NW_FYMC_MX 表的数据。

    Args:
        dwbh (str): 单位编号。
        model (str): 模型。
        kjnd (str): 会计年度。

    Returns:
        list: 包含计算结果的数据列表。
    """
    nw_fymc_data = nw_fymc_service.get_data_by_condition(p_dwbh, p_model, p_kjnd)
    # print(nw_fymc_data)
    # print(type(nw_fymc_data))
    con = get_connection()

    # 关闭自动提交，开始一个显式的事务
    # con.execute("BEGIN TRANSACTION")
    con.execute("DELETE FROM NW_FYMC_MX "
                " WHERE dwbh = $p_dwbh and model = $p_model "
                "   and (kjnd = $p_kjnd or $p_kjnd is null) ",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_kjnd':p_kjnd})
    # 提交事务
    # con.execute("COMMIT")
    # 如果有错误，可以回滚事务
    # con.execute("ROLLBACK")
    # 关闭连接
    con.close()

    for row in nw_fymc_data:
        # dwbh = row[1]
        # model = row[2]
        l_kjnd = row[3] if p_kjnd is None else p_kjnd
        l_fybh = row[4]
        l_fymc = row[5]
        l_kmbh_desc = row[6]  # 原数据填写规则：JFFS#501101,+JFFS#501102,-JFFS#501108
                            # 新规则示例1：K:+501101#JFFS,+501102#DFFS,-501108#NCYE   , 说明: 开头字母K加冒号(:)表示为科目描述，后面跟着 '运算方式''科目编号'#'取数方式'，多个表达式以逗号(,)分割
                            # 新规则示例2：501101#JFFS,501102#DFFS,-501108#NCYE       , 说明: 开头字母K可省略; 运算方式中的加号可省略
                            # 新规则示例3：F:+C010,+C020,-C030,-C040                  , 说明: 开头字母F表示列运算表达式，其后的C010表示同一模型下的其他列编号
        l_status = row[7]
        # create_by = row[8]
        # #remarks = row[9]

        # print(row)
        # 计算一行
        calc_nw_fymc_one_row(p_dwbh, p_model, l_kjnd, l_fybh, l_fymc, l_kmbh_desc, l_status)



def calc_nw_fymc_one_row(p_dwbh, p_model, p_kjnd, p_fybh, p_fymc, p_kmbh_desc, p_status, p_over_calc='+'):

    # 新规则示例1：K:+501101#JFFS,+501102#DFFS,-501108#NCYE   , 说明: 开头字母K加冒号(:)表示为科目描述，后面跟着 '运算方式''科目编号'#'取数方式'，多个表达式以逗号(,)分割
    # 新规则示例2：501101#JFFS,501102#DFFS,-501108#NCYE       , 说明: 开头字母K可省略; 运算方式中的加号可省略
    # 新规则示例3：F:+C010,+C020,-C030,-C040                  , 说明: 开头字母F表示列运算表达式，其后的C010表示同一模型下的其他列编号



    trim_kmbh_desc = re.sub(r'\s+', '', p_kmbh_desc).upper()
    desc_type = 'K'
    if trim_kmbh_desc.startswith('K:'):
        desc_type = 'K'
        trim_kmbh_desc = trim_kmbh_desc.replace('K:', '')
    elif trim_kmbh_desc.startswith('F:'):
        desc_type = 'F'
        trim_kmbh_desc = trim_kmbh_desc.replace('F:', '')
    else:
        desc_type = 'K'

    # print(desc_type)
    # print(trim_kmbh_desc)
    if desc_type == 'K':
        # +501101#JFFS,+501102#DFFS,-501108#NCYE
        for one_kmbh in trim_kmbh_desc.split(','):
            if one_kmbh[0] == '-':
                l_calc = '-'
                one_kmbh_without_flag = one_kmbh[1:]
            elif one_kmbh[0] == '+':
                l_calc = '+'
                one_kmbh_without_flag = one_kmbh[1:]
            else:
                l_calc = '+'
                one_kmbh_without_flag = one_kmbh
            # 相斥为负
            if p_over_calc != l_calc:
                l_calc = '-'
            else:
                l_calc = '+'

            l_kmbh = re.findall(r'\d+', one_kmbh_without_flag)
            if len(l_kmbh) == 0:
                raise Exception("列："+p_fybh+p_fymc+"的数据描述："+p_kmbh_desc+" 中存在错误,无法分析出科目编号")
            l_kmbh = l_kmbh[0]

            jsfs_matches = re.findall(r'[a-zA-Z]+', one_kmbh_without_flag)
            l_jsfs = jsfs_matches[0] if jsfs_matches else 'JFFS'
            # print(l_calc, l_jsfs, l_kmbh)
            add_data(uuid.uuid4(), p_dwbh, p_model, p_kjnd, p_fybh, p_fymc, l_kmbh, l_calc, l_jsfs, p_status, 'sys', '自动计算得出')
    elif desc_type == 'F':
        # 由其他列计算的表达式计算 +C010,+C020,-C030,-C040
        for one_col in trim_kmbh_desc.split(','):
            if one_col[0] == '-':
                l_calc = '-'
                l_fybh = one_col[1:]
            elif one_col[0] == '+':
                l_calc = '+'
                l_fybh = one_col[1:]
            else:
                l_calc = '+'
                l_fybh = one_col
            # 相斥为负
            if p_over_calc != l_calc:
                l_calc = '-'
            else:
                l_calc = '+'

            # print(l_calc, l_fybh)
            l_kmbh_desc = nw_fymc_service.get_kmbh_desc(p_dwbh, p_model, p_kjnd, l_fybh)
            # 用对应列的科目描述表达式递归计算本列应分解的费用配置明细
            calc_nw_fymc_one_row(p_dwbh, p_model, p_kjnd, p_fybh, p_fymc, l_kmbh_desc, p_status, l_calc)



